from osv import osv, fields

class kderp_reports_quotation_list(osv.osv):
     _name = "kderp.reports.quotation.list"
     _description ="KDERP Reports Quotation List"
     _auto = False
     _columns = {
                   # "id":fields.many2one("sale.order","Quotation"),
                    "est_idr":fields.float("Est. IDR"),
                    "est_usd":fields.float("Est. USD"),
                    "est_jpy":fields.float("Est. JPY"),
                    "est_eur":fields.float("Est. EUR"),
                    "est_sgd":fields.float("Est. SGD"),
                    "contr_idr":fields.float("Contr. IDR"),
                    "contr_usd":fields.float("Contr. USD"),
                    "contr_jpy":fields.float("Contr. JPY"),
                    "contr_eur":fields.float("Contr. EUR"),
                    "contr_sgd":fields.float("Contr. SGC")
                 }
     def init(self, cr):
         cr.execute("""create or replace view kderp_reports_quotation_list as 
                        select 
                            ps.id,
                            sum(ps."est_idr") as "est_idr",
                            sum(ps."est_usd") as "est_usd",
                            sum(ps."est_jpy") as "est_jpy",
                            sum(ps."est_eur") as "est_eur",
                            sum(ps."est_sgd") as "est_sgd",
                            sum(ps."contr_idr") as "contr_idr",
                            sum(ps."contr_usd") as "contr_usd",
                            sum(ps."contr_jpy") as "contr_jpy",
                            sum(ps."contr_eur") as "contr_eur",
                            sum(ps."contr_sgd") as "contr_sgd"
                        from
                            (select 
                                so.id as id,
                                sum (case when kic.code = 'IDR' then qsl.amount else 0 end) as "est_idr",
                                sum (case when kic.code = 'USD' then qsl.amount else 0 end) as "est_usd",
                                sum (case when kic.code = 'JPY' then qsl.amount else 0 end) as "est_jpy",
                                sum (case when kic.code = 'EUR' then qsl.amount else 0 end) as "est_eur",
                                sum (case when kic.code = 'SGD' then qsl.amount else 0 end) as "est_sgd",
                                sum (case when kic.code = 'IDR' then 0 else 0 end) as "contr_idr",
                                sum (case when kic.code = 'USD' then 0 else 0 end) as "contr_usd",
                                sum (case when kic.code = 'JPY' then 0 else 0 end) as "contr_jpy",
                                sum (case when kic.code = 'EUR' then 0 else 0 end) as "contr_eur",
                                sum (case when kic.code = 'SGD' then 0 else 0 end) as "contr_sgd"
                                --sum(qsl.amount) as subtotal_submit
                            from 
                                sale_order so
                            left join 
                                quotation_submit_line qsl on so.id = qsl.order_id
                            left join 
                                kderp_currency kic on qsl.currency_id = kic.id
                            group by 
                                so.id
                            union
                            select 
                                so.id as id,
                                sum (case when kic.code = 'IDR' then 0 else 0 end) as "est_idr",
                                sum (case when kic.code = 'USD' then 0 else 0 end) as "est_usd",
                                sum (case when kic.code = 'JPY' then 0 else 0 end) as "est_jpy",
                                sum (case when kic.code = 'EUR' then 0 else 0 end) as "est_eur",
                                sum (case when kic.code = 'SGD' then 0 else 0 end) as "est_sgd",
                                sum (case when kic.code = 'IDR' then sol.amount+sol.discount else 0 end) as "contr_idr",
                                sum (case when kic.code = 'USD' then sol.amount+sol.discount else 0 end) as "contr_usd",
                                sum (case when kic.code = 'JPY' then sol.amount+sol.discount else 0 end) as "contr_jpy",
                                sum (case when kic.code = 'EUR' then sol.amount+sol.discount else 0 end) as "contr_eur",
                                sum (case when kic.code = 'SGD' then sol.amount+sol.discount else 0 end) as "contr_sgd"
                            from 
                                sale_order so
                            left join 
                                sale_order_line sol on so.id = sol.order_id
                            left join 
                                kderp_currency kic on sol.currency_id = kic.id
                            group by so.id) ps
                        group by ps.id""")
kderp_reports_quotation_list()
